package cn.edu.zjgsu.ito.service;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import cn.edu.zjgsu.ito.model.Course;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminCouManageService {

	/**
	 * 根据上传的EXCEL表格，获取所有课程信息并存入数据库
	 * 黄凯航
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public String readCourseTemplate(String fileName,String newFileName,String filePath) throws BiffException, IOException
	{
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i=0; //列
		int j=1; //行
		int k=-1;
		int rows=0;
		int breakRows=1;
		int breakExcel=1;
		int breakK=0;
		int breakNo=0;
		int breakName=0;
		int baseOnNo=0;
		int baseOnName=0;
		int countSuccess=0;
		int countUpdate=0;
		int countFail=0;

		String error="";
		String breakCourseNo="";
		String breakCourseName="";
		String collegeName="";
		String type="Course Template（课程模板）";
		String uploader="Admin（教学秘书）";
		String uploadTime=format.format(date);

		boolean intValid1=false;
		boolean intValid2=false;

		Record uploadFileRecord=new Record().set("type", type)
				.set("fileName", fileName)
				.set("newFileName", newFileName)
				.set("filePath", filePath)
				.set("uploader", uploader)
				.set("uploadTime", uploadTime);
		Db.save("upload_file", uploadFileRecord);

		Workbook rwb=null;
		Cell[] cells=new Cell[7];
		InputStream stream=new FileInputStream(filePath+"\\"+newFileName);
		rwb=Workbook.getWorkbook(stream);
		Sheet sheet=rwb.getSheet(0);
		cells[0]=sheet.getCell(0,j);
		cells[1]=sheet.getCell(1,j);
		rows=sheet.getRows();

		String[] courseNos=new String[rows-1];
		String[] courseNames=new String[rows-1];

		while(true)
		{
			breakRows++;

			if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
					cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY)
			{
				if(breakRows==rows)
					break;
				j++;
				cells[0]=sheet.getCell(0, j);
				cells[1]=sheet.getCell(1, j);
				continue;
			}
			k++;
			courseNos[k]=cells[0].getContents().trim();
			courseNames[k]=cells[1].getContents().trim();
			breakK=k;
			if(breakRows==rows)
				break;
			j++;
			cells[0]=sheet.getCell(0, j);
			cells[1]=sheet.getCell(1, j);
		}

		for(j=0;j<=breakK;j++)
		{
			if(breakExcel==0)
				break;
			for(k=j+1;k<=breakK;k++)
			{
				if(courseNos[j].equals(courseNos[k]))
				{
					breakExcel=0;
					breakCourseNo=courseNos[j];
					breakNo=1;
					break;
				}
				if(courseNames[j].equals(courseNames[k]))
				{
					breakExcel=0;
					breakCourseName=courseNames[j];
					breakName=1;
					break;
				}
			}
		}

		if(breakExcel==1)
		{
			breakRows=1;
			j=1;
			cells[0]=sheet.getCell(0, j);
			cells[1]=sheet.getCell(1, j);
			cells[2]=sheet.getCell(2, j);
			cells[3]=sheet.getCell(3, j);
			cells[4]=sheet.getCell(4, j);
			cells[5]=sheet.getCell(5, j);
			cells[6]=sheet.getCell(6, j);

			while(true)
			{
				baseOnNo=0;
				baseOnName=0;
				breakRows++;
				if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
						cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY||
						cells[2].getContents().trim().equals("")||cells[2].getType()==CellType.EMPTY||
						cells[3].getContents().trim().equals("")||cells[3].getType()==CellType.EMPTY||
						cells[4].getContents().trim().equals("")||cells[4].getType()==CellType.EMPTY||
						cells[5].getContents().trim().equals("")||cells[5].getType()==CellType.EMPTY)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0, j);
						cells[1]=sheet.getCell(1, j);
						cells[2]=sheet.getCell(2, j);
						cells[3]=sheet.getCell(3, j);
						cells[4]=sheet.getCell(4, j);
						cells[5]=sheet.getCell(5, j);
						cells[6]=sheet.getCell(6, j);
					}
					continue;
				}

				List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",cells[2].getContents().trim());
				if(collegeInfo.size()==0)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0, j);
						cells[1]=sheet.getCell(1, j);
						cells[2]=sheet.getCell(2, j);
						cells[3]=sheet.getCell(3, j);
						cells[4]=sheet.getCell(4, j);
						cells[5]=sheet.getCell(5, j);
						cells[6]=sheet.getCell(6, j);
					}
					continue;
				}
				collegeName=collegeInfo.get(0).get("collegeName");

				intValid1=isInt(cells[3].getContents().trim());
				intValid2=isInt(cells[4].getContents().trim());
				if(intValid1==false||intValid2==false)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0, j);
						cells[1]=sheet.getCell(1, j);
						cells[2]=sheet.getCell(2, j);
						cells[3]=sheet.getCell(3, j);
						cells[4]=sheet.getCell(4, j);
						cells[5]=sheet.getCell(5, j);
						cells[6]=sheet.getCell(6, j);
					}
					continue;
				}

				List<Record> courseInfo=Db.find("select * from course");

				for(i=0;i<courseInfo.size();i++)
				{
					Record singleCourseInfo=courseInfo.get(i);
					String courseNo=singleCourseInfo.getStr("courseNo");
					if(courseNo.equals(cells[0].getContents().trim()))
					{
						List<Record> coursetemp=Db.find("select * from course where courseName=?",cells[1].getContents().trim());
						if(coursetemp.size()==0)
						{
							baseOnNo=1;
							break;
						}
						else
						{
							countFail++;
							break;
						}
					}
				}

				if(baseOnNo==0)
				{
					for(i=0;i<courseInfo.size();i++)
					{
						Record singleCourseInfo=courseInfo.get(i);
						String courseName=singleCourseInfo.get("courseName");
						if(courseName.equals(cells[1].getContents().trim()))
						{
							baseOnName=1;
							break;
						}
					}
				}

				if(baseOnNo==0&&baseOnName==0)
				{
					Record courseRecord=new Record()
					.set("courseNo", cells[0].getContents().trim())
					.set("courseName", cells[1].getContents().trim())
					.set("collegeCode", cells[2].getContents().trim())
					.set("collegeName", collegeName)
					.set("credit", cells[3].getContents().trim())
					.set("period", cells[4].getContents().trim())
					.set("courseType", cells[5].getContents().trim())
					.set("introduction", cells[6].getContents().trim());
					Db.save("course", courseRecord);
					countSuccess++;
				}

				else
				{
					if(baseOnNo==1)
					{
						Db.update("update course set courseName=?,collegeCode=?,collegeName=?,credit=?,period=?,courseType=?,introduction=? where courseNo=?",
								cells[1].getContents().trim(),
								cells[2].getContents().trim(),
								collegeName,
								cells[3].getContents().trim(),
								cells[4].getContents().trim(),
								cells[5].getContents().trim(),
								cells[6].getContents().trim(),
								cells[0].getContents().trim());
						boolean WARMING=updateAllTablesByNo(cells[0].getContents().trim(),cells[1].getContents().trim());
						if(WARMING==false)
							System.out.println("出事了！！！");
						countUpdate++;
					}
					else
					{
						if(baseOnName==1)
						{
							Db.update("update course set courseNo=?,collegeCode=?,collegeName=?,credit=?,period=?,courseType=?,introduction=? where courseName=?",
									cells[0].getContents().trim(),
									cells[2].getContents().trim(),
									collegeName,
									cells[3].getContents().trim(),
									cells[4].getContents().trim(),
									cells[5].getContents().trim(),
									cells[6].getContents().trim(),
									cells[1].getContents().trim());
							boolean WARMING=updateAllTablesByName(cells[0].getContents().trim(),cells[1].getContents().trim());
							if(WARMING==false)
								System.out.println("出事了！！！");
							countUpdate++;
						}
					}
				}
				j++;
				if(breakRows==rows)
					break;
				else
				{
					cells[0]=sheet.getCell(0, j);
					cells[1]=sheet.getCell(1, j);
					cells[2]=sheet.getCell(2, j);
					cells[3]=sheet.getCell(3, j);
					cells[4]=sheet.getCell(4, j);
					cells[5]=sheet.getCell(5, j);
					cells[6]=sheet.getCell(6, j);
				}
			}
			error="成功导入"+countSuccess+"条记录;成功覆盖"+countUpdate+"条记录;导入失败"+countFail+"条记录";
			return error;
		}
		else
		{
			if(breakNo==1)
				error="所上传的EXCEL表中存在数据冲突 ——课程编号："+breakCourseNo;	
			else
			{
				if(breakName==1)
					error="所上传的EXCEL表中存在数据冲突 ——课程名称："+breakCourseName;	
			}
			return error;
		}
	}

	/**
	 * 单个课程添加方法
	 * 黄凯航
	 * @param course
	 * @return
	 */
	public String addSingleCourse(Course course)
	{
		String courseNo=course.getStr("courseNo").trim();
		String courseName=course.getStr("courseName").trim();
		String collegeCode=course.getStr("collegeCode").trim();
		String credit=String.valueOf(course.getInt("credit")).trim();
		String period=String.valueOf(course.getInt("period")).trim();
		String courseType=course.getStr("courseType").trim();
		String collegeName="";
		String error="";
		boolean intValid1=false;
		boolean intValid2=false;
		
		if(courseNo.equals("")||courseNo==null||
				courseName.equals("")||courseName==null||
				collegeCode.equals("")||collegeCode==null||
				credit.equals("")||credit==null||
				period.equals("")||period==null||
				courseType.equals("")||courseType==null)
		{
			error="请填写所有必填信息！";
			return error;
		}

		intValid1=isInt(credit);
		intValid2=isInt(period);
		if(intValid1==false||intValid2==false){
			error="数字字段请填写数字";
			return error;
		}

		List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",collegeCode);
		collegeName=collegeInfo.get(0).get("collegeName");
		course.set("collegeName", collegeName);

		List<Record> courses=Db.find("select * from course where courseNo=?",courseNo);
		List<Record> coursetemp=Db.find("select * from course where courseName=?",courseName);
		if(courses.size()==0&&coursetemp.size()==0)
		{
			course.save();
			error="新增成功";
		}
		if(courses.size()==1&&coursetemp.size()==0)
		{
			Db.update("update course set courseName=?,collegeCode=?,collegeName=?,credit=?,period=?,courseType=?,introduction=? where courseNo=?",
					courseName,
					collegeCode,
					collegeName,
					credit,
					period,
					courseType,
					course.getStr("introduction"),
					courseNo);
			boolean WARMING=updateAllTablesByNo(courseNo,courseName);
			if(WARMING==false)
				System.out.println("出事了！！！");
			error="新增成功";
		}
		if(courses.size()==0&&coursetemp.size()==1)
		{
			Db.update("update course set courseNo=?,collegeCode=?,collegeName=?,credit=?,period=?,courseType=?,introduction=? where courseName=?",
					courseNo,
					collegeCode,
					collegeName,
					credit,
					period,
					courseType,
					course.getStr("introduction"),
					courseName);
			boolean WARMING=updateAllTablesByName(courseNo,courseName);
			if(WARMING==false)
				System.out.println("出事了！！！");
			error="新增成功";
		}
		if(courses.size()==1&&coursetemp.size()==1)
			error="新增失败："+courseNo+"和"+courseName+"皆已存在";
		return error;	
	}

	/**
	 * 单个课程更新方法
	 * 黄凯航
	 * @param course
	 * @return
	 */
	public String updateSingleCourse(Course course)
	{
		int id=course.getInt("id");
		String courseNo=course.getStr("courseNo").trim();
		String courseName=course.getStr("courseName").trim();
		String collegeCode=course.getStr("collegeCode").trim();
		String credit=String.valueOf(course.getInt("credit")).trim();
		String period=String.valueOf(course.getInt("period")).trim();
		String courseType=course.getStr("courseType").trim();
		String collegeName="";
		String error="";
		boolean intValid1=false;
		boolean intValid2=false;
		
		if(courseNo.equals("")||courseNo==null||
				courseName.equals("")||courseName==null||
				collegeCode.equals("")||collegeCode==null||
				credit.equals("")||credit==null||
				period.equals("")||period==null||
				courseType.equals("")||courseType==null)
		{
			error="请填写所有必填信息！";
			return error;
		}

		intValid1=isInt(credit);
		intValid2=isInt(period);
		if(intValid1==false||intValid2==false){
			error="数字字段请填写数字";
			return error;
		}
		
		List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",collegeCode);
		collegeName=collegeInfo.get(0).get("collegeName");
		course.set("collegeName", collegeName);

		List<Record> courses=Db.find("select * from course where courseName=?",courseName);	
		if(courses.size()==0)
		{
			course.update();
			boolean WARMING=updateAllTablesByNo(courseNo,courseName);
			if(WARMING==false)
				System.out.println("出事了！！！");
			error="修改成功";		
		}
		else
		{
			if(courses.size()==1)
			{
				if(courses.get(0).getInt("id")==id)
				{
					course.update();
					boolean WARMING=updateAllTablesByNo(courseNo,courseName);
					if(WARMING==false)
						System.out.println("出事了！！！");
					error="修改成功";	
				}
				else
					error="课程名称已存在，修改失败";
			}
		}
		return error;
	}
	/**
	 * 课程删除方法
	 * @param courseNo
	 */
	public void deleteOrNot(String courseNo)
	{
		int courseEnd=1;
		int roster=1;
		int studentGrade=1;
		int studentTimetable=1;
		int teacherTimetable=1;
		int majorTimetable=1;
		List<Record> ceNullOrNot=Db.find("select * from course_end where courseNo=?",courseNo);
		List<Record> rNullOrNot=Db.find("select * from roster where courseNo=?",courseNo);
		List<Record> sgNullOrNot=Db.find("select * from student_grade where courseNo=?",courseNo);
		List<Record> stNullOrNot=Db.find("select * from student_timetable where courseNo=?",courseNo);
		List<Record> ttNullOrNot=Db.find("select * from teacher_timetable where courseNo=?",courseNo);
		List<Record> mtNullOrNot=Db.find("select * from major_timetable where courseNo=?",courseNo);
		if(ceNullOrNot.size()==0)
			courseEnd=0;
		if(rNullOrNot.size()==0)
			roster=0;
		if(sgNullOrNot.size()==0)
			studentGrade=0;
		if(stNullOrNot.size()==0)
			studentTimetable=0;
		if(ttNullOrNot.size()==0)
			teacherTimetable=0;
		if(mtNullOrNot.size()==0)
			majorTimetable=0;
		
		if(courseEnd==0&&roster==0&&studentGrade==0&&studentTimetable==0&&teacherTimetable==0&&majorTimetable==0)
			Db.update("delete from course where courseNo=?",courseNo);
	}


	/**
	 * 整数判断方法
	 * @param str
	 * @return
	 */
	public static boolean isInt(String str){
		try{
			Integer.parseInt(str);
			if(!(Integer.parseInt(str)>=0&&Integer.parseInt(str)<=200))
				return false;
		}catch(Exception e){
			return false;
		}
		return true;
	}

	/**
	 * 更新所有表格一
	 * 黄凯航
	 */
	public boolean updateAllTablesByNo(String courseNo,String courseName)
	{
		int caseOne=Db.update("update course_end set courseName=? where courseNo=?",courseName,courseNo);
		int caseTwo=Db.update("update roster set courseName=? where courseNo=?",courseName,courseNo);
		int caseThree=Db.update("update major_timetable set courseName=? where courseNo=?",courseName,courseNo);
		int caseFour=Db.update("update teacher_timetable set courseName=? where courseNo=?",courseName,courseNo);
		int caseFive=Db.update("update student_timetable set courseName=? where courseNo=?",courseName,courseNo);

		if(caseOne>0&&caseTwo>0&&caseThree>0&&caseFour>0&&caseFive>0)
			return true;
		else
			return false;
	}

	/**
	 * 更新所有表格二
	 * 黄凯航
	 */
	public boolean updateAllTablesByName(String courseNo,String courseName)
	{
		int caseOne=Db.update("update course_end set courseNo=? where courseName=?",courseNo,courseName);
		int caseTwo=Db.update("update roster set courseNo=? where courseName=?",courseNo,courseName);
		int caseThree=Db.update("update major_timetable set courseNo=? where courseName=?",courseNo,courseName);
		int caseFour=Db.update("update teacher_timetable set courseNo=? where courseName=?",courseNo,courseName);
		int caseFive=Db.update("update student_timetable set courseNo=? where courseName=?",courseNo,courseName);

		if(caseOne>0&&caseTwo>0&&caseThree>0&&caseFour>0&&caseFive>0)
			return true;
		else
			return false;
	}
}
